package com.fjec.market.mapper.sqlbuilder;

import java.util.List;

public class SkuSqlBuilder {
    public String getTop2SkusByBusiIds(final List<Long> farmerIds) {
        if (farmerIds.size() == 0) return null;
        StringBuilder sb = new StringBuilder();
        farmerIds.forEach(id -> sb.append(" union all select * from (select sku.* from info_sku sku, info_spu spu where sku.BusiId = ").append(id).append(" and sku.DelFlag = 0 and sku.SpuId = spu.Id ORDER BY spu.Weight limit 0, 2) tmp").append(id));
        return sb.delete(0, 11).toString();
    }

    public String getSkusByBusiIds(final List<Long> farmerIds) {
        if (farmerIds.size() == 0) return null;
        StringBuilder sb = new StringBuilder();
        farmerIds.forEach(id -> sb.append(" union all select * from (select sku.* from info_sku sku, info_spu spu where sku.BusiId = ").append(id).append(" and sku.DelFlag = 0 and sku.SpuId = spu.Id ORDER BY spu.Weight ) tmp").append(id));
        return sb.delete(0, 11).toString();
    }

    public String getSkusBySpuIDsAndMarkCodeAndBusiType(int markCode, int busiType, List<Long> spuIds, int orderBy, int dir) {
        StringBuilder sb = new StringBuilder("select ");
        if (orderBy == 1)
            sb.append(" SQL_NO_CACHE ");
        sb.append("  distinct sku.* from info_sku sku, info_spu spu, ref_sku_market rsm, ");

        if (busiType == 2)
            sb.append(" info_farmer farmer where sku.BusiId=farmer.Id and farmer.DepoStatus=1 ");
        else
            sb.append(" info_business business where sku.BusiId=business.Id and business.DepoStatus=1 ");

        sb.append(" and sku.DelFlag = 0 and sku.SpuId=spu.Id and spu.Id=rsm.SpuId ");

        if (busiType != 0) {
            sb.append(" and sku.BusiType = ").append(busiType);
        }

        if (markCode != 0) sb.append(" and rsm.MarkCode=").append(markCode);

        if (spuIds.size() != 0) {
            sb.append(" and spu.Id in (");
            for (Long id : spuIds) sb.append(id + ",");
            sb.deleteCharAt(sb.length() - 1);
            sb.append(")");
        }

        if (orderBy == 1) {
            sb.append(" order by rand() ");
        } else if (orderBy == 2) {
            sb.append(" order by sku.SaleNum ");
        } else if (orderBy == 3) {
            sb.append(" order by sku.RetailPrice ");
        }

        if (dir == 1) {
            sb.append(" asc ");
        } else if (dir == 2) {
            sb.append(" desc ");
        }

        return sb.toString();
    }

    public String getRetailFruits(Integer markCode, List<Long> spuIds, Integer orderBy, Integer dir) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ( ");
        sb.append(" select SQL_NO_CACHE distinct sku.* from info_sku sku, info_spu spu, ref_sku_market rsm, info_farmer farmer ");
        sb.append(" where sku.BusiId=farmer.Id and farmer.DepoStatus=1 and sku.DelFlag = 0 and sku.SpuId=spu.Id and spu.Id=rsm.SpuId");
        sb.append(" and sku.BusiType = 2 ");
        if (markCode != 0)
            sb.append(" and rsm.MarkCode = ").append(markCode);
        if (spuIds.size() != 0) {
            sb.append(" and spu.Id in (");
            for (Long id : spuIds) sb.append(id + ",");
            sb.deleteCharAt(sb.length() - 1);
            sb.append(")");
        }

        sb.append(" union all ");

        sb.append(" select SQL_NO_CACHE distinct sku.* from info_sku sku, info_spu spu, ref_sku_market rsm, info_business business ");
        sb.append(" where business.BusiType = 3 and sku.BusiId=business.Id and business.DepoStatus=1 and sku.DelFlag = 0 and sku.SpuId=spu.Id and spu.Id=rsm.SpuId ");
        sb.append(" and sku.BusiType = 3 ");
        if (markCode != 0)
            sb.append(" and rsm.MarkCode = ").append(markCode);
        if (spuIds.size() != 0) {
            sb.append(" and spu.Id in (");
            for (Long id : spuIds) sb.append(id + ",");
            sb.deleteCharAt(sb.length() - 1);
            sb.append(")");
        }

        sb.append(" ) tmp ");

        if (orderBy == 1) {
            sb.append(" order by rand() ");
        } else if (orderBy == 2) {
            sb.append(" order by SaleNum ");
        } else if (orderBy == 3) {
            sb.append(" order by RetailPrice ");
        }

        if (dir == 1) {
            sb.append(" asc ");
        } else if (dir == 2) {
            sb.append(" desc ");
        }

        return sb.toString();
    }
}
